﻿/****************************************************************************
*                                                                           *
* Copyright (c) Ionescu M. Marius. All rights reserved(2010).               *
*                                                                           *
****************************************************************************/

using System;
using System.Collections;
using System.Data;
using System.IO;
using System.Runtime.InteropServices;
using System.Text;
using System.Web.Security;
using Excel = Microsoft.Office.Interop.Excel;

public partial class Tools_ExportExcel : System.Web.UI.Page
{
	Excel.ApplicationClass open_xls;
	Excel.Workbook newWorkBook;
	Excel.Workbooks allWorkBooks;
	Excel.Sheets sheet;
	Excel.Worksheet prim_WorkSheet;
	Excel.Range range_cell;

	private string GetUserID()
	{
		if (Session["UserID"] == null)
		{
			Session["UserID"] = Tools.GetUserID(GetUserName(), "");
		}
		if (Session["UserID"] != null)
		{
			return Session["UserID"].ToString();
		}
		else
		{
			FormsAuthentication.SignOut();
			Response.Redirect(this.Page.Request.Url.ToString());
			return null;
		}
	}

	private string GetUserName()
	{
		return System.Threading.Thread.CurrentPrincipal.Identity.Name;
	}

	/// <summary>
	/// Returneaza numarul de linii din header.
	/// </summary>
	private int GetHeaderNo(ArrayList arColumns, DataSet ds, int nSessionKeyColumnHeadersIndex)
	{
		int n = 0;

		for (int i = 0; i < arColumns.Count; ++i)
		{
			int x = GetDataFieldHeaderName(arColumns[i].ToString(), ds, nSessionKeyColumnHeadersIndex).Split(new char[] { '$' }).Length;
			if (x > n)
				n = x;
		}

		return n;
	}

	/// <summary>
	/// Preia corespondenta numelui coloanei din baza de date.
	/// </summary>
	/// <param name="strField">Numele coloanei</param>
	/// <param name="ds">Datasetul</param>
	/// <param name="nSessionKeyColumnHeadersIndex">Indexul tabelei cu corespondenta</param>
	private string GetDataFieldHeaderName(string strField, DataSet ds, int nSessionKeyColumnHeadersIndex)
	{
		if (nSessionKeyColumnHeadersIndex > 0 && ds.Tables.Count > nSessionKeyColumnHeadersIndex && ds.Tables[nSessionKeyColumnHeadersIndex].Columns.Contains("ColumnName_ID") && ds.Tables[nSessionKeyColumnHeadersIndex].Columns.Contains("ColumnName"))
		{
			DataView dv = new DataView(ds.Tables[nSessionKeyColumnHeadersIndex]);
			dv.RowFilter = "ColumnName_ID = '" + strField + "'";
			if (dv.Count > 0)
				strField = dv[0]["ColumnName"].ToString();
		}
		return strField;
	}

	protected void Page_Load(object sender, EventArgs e)
	{
		DataSet dsValues;
		if (Request.QueryString["s"] != null && Request.QueryString["s"] != "")
			dsValues = (DataSet)Session[Request.QueryString["s"]];		// Vin dintr-un grid oarecare
		else
			dsValues = (DataSet)Session["ds_ReportResult"];				// Vin din pagina de rapoarte

		if (dsValues == null)
		{// A expirat sesiunea!
			Response.ClearContent();
			Response.ClearHeaders();
			Response.Clear();
			Response.Write("<b style='color: Red'>" + Resources.GridView.GenerateAgainTheReport + "...</b>");
			Response.Flush();
			Response.Close();
		}

		string strRptID = "";
		string strRptName = "";
		string[] arHiddenColumns = new string[] { };
		string strTotalColumns = "";
		bool bTotalOnAllDecimalColumns = false;
		string[] arShortDateColumns = new string[] { };
		string[] arFourDigitsColumns = new string[] { };
		string[] arPercentColumns = new string[] { };
		string[] arColumnFormat = new string[] { };
		string[] arColumnFormatNames = new string[] { };
		string[] arTranslateColumns = new string[] { };
		int nSessionKeyColumnHeadersIndex = -1;

		DataView dvValues = new DataView(dsValues.Tables[0]);

		// Preia proprietatile gridului de exportat
		string strClientID = Request.QueryString["c"];
		if (strClientID != null && strClientID != "")
		{
			if (Session[strClientID + "_CaptionForXLS"] != null)
				strRptName = (string)Session[strClientID + "_CaptionForXLS"];
			if (Session[strClientID + "_HiddenColumns"] != null)
				arHiddenColumns = (string[])Session[strClientID + "_HiddenColumns"];
			if (Session[strClientID + "_RowFilter"] != null)
				dvValues.RowFilter = (string)Session[strClientID + "_RowFilter"];
			if (Session[strClientID + "_SortExpression"] != null && (string)Session[strClientID + "_SortExpression"] != "")
				dvValues.Sort = string.Format("{0} {1}", Session[strClientID + "_SortExpression"], Session[strClientID + "_SortDirection"]);
			if (Session[strClientID + "_SessionKeyColumnHeadersIndex"] != null)
				nSessionKeyColumnHeadersIndex = (int)Session[strClientID + "_SessionKeyColumnHeadersIndex"];
		}
		string strTemplateFileName = Request.QueryString["t"];

		GC.Collect();
		GC.WaitForPendingFinalizers();

		string strGuid = System.Guid.NewGuid().ToString().ToUpper();
		string strCurrentDir = Server.MapPath("./TempFiles") + "\\";

		open_xls = new Excel.ApplicationClass();
		IntPtr iHandle = new IntPtr(Convert.ToInt32(open_xls.Parent.Hwnd));

		// Initializeaza Excel-ul
		open_xls.Caption = strGuid;
		allWorkBooks = open_xls.Workbooks;
		if (strTemplateFileName != null && strTemplateFileName != "")
			newWorkBook = open_xls.Workbooks.Open(strCurrentDir + strTemplateFileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
		else
			newWorkBook = (Excel.Workbook)(allWorkBooks.Add(Excel.XlWBATemplate.xlWBATWorksheet));
		sheet = newWorkBook.Worksheets;
		prim_WorkSheet = (Excel.Worksheet)sheet.get_Item(1);

//		if (Request.QueryString["s"] == null || Request.QueryString["s"] == "")
		{
			DataSet ds = (DataSet)Session["ds_ReportDetails"];

			if (ds != null)
			{
				DataView dv0 = ds.Tables[0].DefaultView;

				strRptID = dv0[0]["ID"].ToString();
				if (strRptName == "")
					strRptName = dv0[0]["Name_" + Tools.GetLanguage(Request)].ToString();
				if (strClientID == null || strClientID == "" || Session[strClientID + "_HiddenColumns"] == null)
					arHiddenColumns = dv0[0]["HiddenColumns"].ToString().Split(new char[] { ',' });
				strTotalColumns = dv0[0]["TotalColumns"].ToString();
				bTotalOnAllDecimalColumns = Convert.ToBoolean(dv0[0]["TotalOnAllDecimalColumns"]);
				arShortDateColumns = dv0[0]["ShortDateColumns"].ToString().Split(new char[] { ',' });
				arFourDigitsColumns = dv0[0]["FourDigitsColumns"].ToString().Split(new char[] { ',' });
				arPercentColumns = dv0[0]["PercentColumns"].ToString().Split(new char[] { ',' });
				arColumnFormat = dv0[0]["ColumnFormat"].ToString().Split(new char[] { ',' });
				arColumnFormatNames = dv0[0]["ColumnFormatNames"].ToString().Split(new char[] { ',' });
				arTranslateColumns = dv0[0]["TranslateColumns"].ToString().Split(new char[] { ',' });
			}
		}

		ArrayList arColumns = new ArrayList();
		ArrayList arHeaders = new ArrayList();
		ArrayList arFormats = new ArrayList();
/*
		if (dvValues.Table.Columns.Contains("NrCrt"))
		{
			arColumns.Add("NrCrt");
			arHeaders.Add(Tools.GetHeaderText("NrCrt"));
			arFormats.Add("{0:N0}");
		}
*/
		foreach (DataColumn dc in dvValues.Table.Columns)
		{
			if (Tools.ArrayIndexOf(arHiddenColumns, dc.ColumnName) != -1)
				continue;
// 			if (dc.ColumnName == "NrCrt")
// 				continue;

			arColumns.Add(dc.ColumnName);
			arHeaders.Add(Tools.RemoveHTML(Tools.GetHeaderText(GetDataFieldHeaderName(dc.ColumnName, dsValues, nSessionKeyColumnHeadersIndex))));

			int y = Tools.ArrayIndexOf(arColumnFormatNames, dc.ColumnName);
			if (y != -1)
				arFormats.Add(Tools.GetHeaderText(arColumnFormat[y]));
			else if (dc.DataType == Type.GetType("System.Int32") || dc.DataType == Type.GetType("System.Byte"))
				arFormats.Add("{0:N0}");
			else if (dc.DataType == Type.GetType("System.Decimal") || dc.DataType == Type.GetType("System.Float") || dc.DataType == Type.GetType("System.Double") || dc.DataType == Type.GetType("System.Single"))
			{
				if (Tools.ArrayIndexOf(arFourDigitsColumns, dc.ColumnName) != -1)
					arFormats.Add("{0:N4}");
				else if (Tools.ArrayIndexOf(arPercentColumns, dc.ColumnName) != -1)
					arFormats.Add("{0:N} %");
				else
					arFormats.Add("{0:N}");
			}
			else if (dc.DataType == Type.GetType("System.DateTime"))
			{
				int z = Tools.ArrayIndexOf(arShortDateColumns, dc.ColumnName);
				if (z != -1)
					arFormats.Add("{0:yyyy-MM-dd}");
				else
					arFormats.Add("{0:yyyy-MM-dd HH:mm:ss}");
			}
			else
				arFormats.Add("T");
		}

		// Desparte textul din header dupa caracterul $
		int nHeaderNo = GetHeaderNo(arColumns, dsValues, nSessionKeyColumnHeadersIndex);
		object[,] objValues = new object[dvValues.Count + nHeaderNo, arColumns.Count];
		object[,] objValues2 = new object[dvValues.Count + nHeaderNo, arColumns.Count];
		for (int i = 0; i < nHeaderNo; ++i)
			for (int j = 0; j < arHeaders.Count; ++j)
			{
				string strDataField = (string)arHeaders[j];
				string[] arDataField = strDataField.Split(new char[] { '$' });
				if (arDataField.Length > 1 && i < arDataField.Length)
					strDataField = arDataField[i];
				objValues[i, j] = strDataField;

				string strDataField2 = (string)arColumns[j];
				if (strDataField2.StartsWith("Column_"))
					strDataField2 = strDataField2.Substring(("Column_").Length);
				objValues2[i, j] = strDataField2;
				string[] arDataField2 = strDataField2.Split(new char[] { '$' });
				if (arDataField2.Length > 1 && i < arDataField2.Length)
					objValues2[i, j] = string.Join("$", arDataField2, 0, i + 1);
			}

		// Zona header - aliniere
		range_cell = prim_WorkSheet.get_Range("A4", BuildNextAlphabeticCode("A", arHeaders.Count) + (4 + nHeaderNo - 1).ToString());
		range_cell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
		range_cell.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
		range_cell.NumberFormat = "@";

		// Uneste celulele de pe acelasi row care au aceeasi valoare
		for (int i = 0; i < nHeaderNo; ++i)
			if (arColumns.Count > 0)
			{
				int j = 0, k = 0;
				string x = (string)objValues2[i, j];
				++j;
				while (j < arColumns.Count)
				{
					string y = (string)objValues2[i, j];
					if (x == y)
					{
						range_cell = prim_WorkSheet.get_Range(BuildNextAlphabeticCode("A", k + 1) + (4 + i), BuildNextAlphabeticCode("A", j + 1) + (4 + i));
						range_cell.Merge(false);
					}
					else
					{
						k = j;
						x = y;
					}
					++j;
				}
			}
		// Uneste celulele de pe aceeasi coloana care au aceeasi valoare
		for (int j = 0; j < arHeaders.Count; ++j)
		{
			int i = 0, k = 0;
			string x = (string)objValues2[i, j];
			++i;
			while (i < nHeaderNo)
			{
				string y = (string)objValues2[i, j];
				if (x == y)
				{
					range_cell = prim_WorkSheet.get_Range(BuildNextAlphabeticCode("A", (j + 1)) + (4 + k), BuildNextAlphabeticCode("A", (j + 1)) + (4 + i));
					range_cell.Merge(false);
				}
				else
				{
					k = i;
					x = y;
				}
				++i;
			}
		}

		for (int i = 0; i < dvValues.Count; ++i)
		{
			objValues[i + nHeaderNo, 0] = (i + nHeaderNo).ToString();
			for (int j = 0; j < arColumns.Count; ++j)
				if (dvValues[i][arColumns[j].ToString()] != System.DBNull.Value)
					objValues[i + nHeaderNo, j] = dvValues[i][arColumns[j].ToString()].ToString().Substring(0, Math.Min(dvValues[i][arColumns[j].ToString()].ToString().Length, 900));	// Trunchiere la 900 caractere. Ar trebui scoasa!
		}

		// Numele exportului este o celula lata
		range_cell = prim_WorkSheet.get_Range("A2", BuildNextAlphabeticCode("A", arColumns.Count) + 2);
		range_cell.Merge(false);
		range_cell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

		// Numele exportului
		range_cell = prim_WorkSheet.get_Range("A2", "A2");
		range_cell.Value2 = strRptName;
		range_cell.Font.Bold = true;
		range_cell.Font.Size = 12;
		range_cell.NumberFormat = "@";

		range_cell = prim_WorkSheet.get_Range("A4", BuildNextAlphabeticCode("A", arColumns.Count) + (4 + nHeaderNo - 1));
		range_cell.Font.Bold = true;

		// Tipul coloanelor
		for (int j = 0; j < arFormats.Count; ++j)
		{
			range_cell = prim_WorkSheet.get_Range(BuildNextAlphabeticCode("A", j + 1) + "1", BuildNextAlphabeticCode("A", j + 1) + "1");

			switch (arFormats[j].ToString())
			{
				case "{0:yyyy-MM-dd}":
					range_cell.EntireColumn.NumberFormat = "yyyy-MM-dd";
					break;
				case "{0:yyyy-MM-dd HH:mm:ss}":
					range_cell.EntireColumn.NumberFormat = "yyyy-MM-dd HH:mm:ss";
					break;
				case "{0:N}":
					range_cell.EntireColumn.NumberFormat = "#,##0.00";
					break;
				case "{0:N0}":
					range_cell.EntireColumn.NumberFormat = "###0";
					break;
				case "{0:N4}":
					range_cell.EntireColumn.NumberFormat = "#,##0.0000";
					break;
				case "{0:N} %":
					range_cell.EntireColumn.NumberFormat = "#,##0.00 %";
					break;
				case "T":
					range_cell.EntireColumn.NumberFormat = "@";
					break;
				default:
					range_cell.EntireColumn.NumberFormat = "@";
					break;
			}
		}

		// Header-ul este de tip text
		range_cell = prim_WorkSheet.get_Range("A4", BuildNextAlphabeticCode("A", arHeaders.Count) + (4 + nHeaderNo - 1).ToString());
		range_cell.NumberFormat = "@";

		// Seteaza valorile exportate
		range_cell = prim_WorkSheet.get_Range("A4", BuildNextAlphabeticCode("A", arColumns.Count) + ((4 + nHeaderNo - 1) + dvValues.Count));
		range_cell.Cells.Value2 = objValues;

		// Autofit
		range_cell = prim_WorkSheet.get_Range("A4", BuildNextAlphabeticCode("A", arColumns.Count) + ((4 + nHeaderNo - 1) + dvValues.Count));
		range_cell.EntireColumn.AutoFit();

		// Salvare Excel
		string strFile = "Report_" + this.GetUserID() + "_" + strRptID + "__" + System.DateTime.Now.ToString("yyyy_MM_dd__HH_mm_ss__FFF") + ".xls";
		newWorkBook.SaveAs(strCurrentDir + strFile, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Excel.XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
		try
		{// Inchide Excel-ul
			if (newWorkBook != null)
				newWorkBook.Close(null, null, null);
			open_xls.Workbooks.Close();
			open_xls.Quit();
			if (allWorkBooks != null)
				allWorkBooks.Close();
			if (open_xls != null)
				Marshal.ReleaseComObject(open_xls);
			if (prim_WorkSheet != null)
				Marshal.ReleaseComObject(prim_WorkSheet);
			if (newWorkBook != null)
				Marshal.ReleaseComObject(newWorkBook);
			if (sheet != null)
				Marshal.ReleaseComObject(sheet);
			if (allWorkBooks != null)
				Marshal.ReleaseComObject(allWorkBooks);
			prim_WorkSheet = null;
			newWorkBook = null;
			open_xls = null;
			sheet = null;
			allWorkBooks = null;

//			EnsureProcessKilled2(iHandle, strGuid);

			GC.Collect();
			GC.WaitForPendingFinalizers();

			GC.Collect();
			GC.WaitForPendingFinalizers();
		}
		catch (Exception) { throw; }

		// Trimitem excel-ul la client
		Response.ClearContent();
		Response.ClearHeaders();
		Response.Clear();
		Response.ContentType = "application/vnd.ms-excel";
		Response.AddHeader("content-disposition", "attachment; filename=" + strRptName + "_" + DateTime.Now.ToString("yyyy_MM_dd__HH_mm_ss__FFF") + ".xls");
		Response.WriteFile(strCurrentDir + strFile, false);
		Response.Flush();
		Response.Close();

		// Sterge fisierul temporar
		File.Delete(strCurrentDir + strFile);
	}

	/// <summary>
	/// Inchide procesul Excel.
	/// </summary>
	public void EnsureProcessKilled2(IntPtr MainWindowHandle, string Caption)
	{
		Win32.SetLastError(0);
		// for Excel versions <10, this won't be set yet 
		if (IntPtr.Equals(MainWindowHandle, IntPtr.Zero))
		{
			MainWindowHandle = Win32.FindWindow(null, Caption);
		}
		if (IntPtr.Equals(MainWindowHandle, IntPtr.Zero))
			return; // at this point, presume the window has been closed. 
		int iRes, iProcID;
		iProcID = 0;
		iRes = Win32.GetWindowThreadProcessId(MainWindowHandle, ref iProcID);
		if (iProcID == 0) // can’t get Process ID 
		{
			if (Win32.EndTask(MainWindowHandle) != 0) return; // ' success 
			throw new ApplicationException("Failed to close.");
		}

		int ProcessID = iProcID;

		try
		{
			System.Diagnostics.Process proc;
			proc = System.Diagnostics.Process.GetProcessById(ProcessID);
			proc.CloseMainWindow();
			proc.Refresh();
			if (proc.HasExited) return;
			proc.Kill();
			proc.WaitForExit();
		}
		catch (Exception) { }
	}

	public class Win32
	{
		[DllImport("user32.dll")]
		public static extern int EndTask(IntPtr hWnd);
		[DllImport("user32.dll")]
		public static extern IntPtr FindWindow(string lpClassName, string lpWindowName);
		[DllImport("user32.dll")]
		public static extern int GetWindowThreadProcessId(IntPtr hWnd, ref int lpdwProcessId);
		[DllImport("kernel32.dll")]
		public static extern IntPtr SetLastError(int dwErrCode);
	}

	/// <summary>
	/// Returneaza urmatoarea litera pentru a fi folosita ca si nume de coloana in Excel.
	/// </summary>
	public static string BuildNextAlphabeticCode(string current, int n)
	{
		string s = current;
		for (int i = 1; i < n; ++i)
			s = BuildNextAlphabeticCode(s);
		return s;
	}

	/// <summary>
	/// Returneaza urmatoarea litera pentru a fi folosita ca si nume de coloana in Excel.
	/// </summary>
	public static string BuildNextAlphabeticCode(string current)
	{
		StringBuilder ret = new StringBuilder(current);
		bool CA = true; // cary one
		int digit = ret.Length - 1;

		while ((CA) && (digit >= 0))
		{
			char c = current[digit];
			++c;
			if (c > 'Z')
			{
				ret[digit] = 'A';
				CA = true;
			}
			else
			{
				ret[digit] = c;
				CA = false;
			}
			--digit;
		}

		// handle possible overflow
		if (CA) ret.Insert(0, 'A');

		return ret.ToString();
	}
}
